﻿//Mirco Töpfer

#region Usings

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Threading;
using System.Text.RegularExpressions;
using pheidippides.logging.Classes;

#endregion

namespace pheidippides.dataaccess.HelperClasses
{
    public class AccessDbSqlHelper
    {
        private String _command;
        private readonly OleDbConnection _connection;
        private OleDbCommand _oleDbCommand;
        private readonly Dictionary<String, Object> _params;

        #region Constructor

        public AccessDbSqlHelper(String databasePath)
        {
            Logger.GetInstance().LogMessage("Erzeuge neues AccessDBSqlHelper Objekt für DB " + databasePath,
                                            LogLevel.Debug);
            _connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + databasePath);
            _params = new Dictionary<string, object>();
        }

        #endregion

        #region Public Methods


        /// <summary>
        /// Führt eine Datenbankabfrage aus und bringt eine DataTable zurück.
        /// </summary>
        /// <param name="command">SQL Command</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(String command)
        {
            return ExecuteSqlQuery(command).Tables[0];
        }

        public Int32 ExecuteNonQuery(String command)
        {
            return ExecuteSqlNonQuery(command);
        }

        public void SetProperty(Enum e, Object value)
        {
            SetProperty(e.ToString(), value);
        }

        public void SetProperty(String name, Object value)
        {
            if (!name.StartsWith("@")) name = @"@" + name;

            if (value == null || value == DBNull.Value)
            {
                _params.Add(name, DBNull.Value);
            }
            else
            {
                var newValue = value as DateTime?;

                _params.Add(name,
                            newValue != null
                                ? new DateTime(newValue.Value.Year, newValue.Value.Month, newValue.Value.Day,newValue.Value.Hour, newValue.Value.Minute, newValue.Value.Second)
                                : value);
            }
        }

        #endregion

        #region Private Methods

        private void ConfigureParams()
        {
            try
            {
                if (_params.Count == 0) return;

                Regex regex = new Regex(@"\@{1}\w+");

                MatchCollection coll = regex.Matches(_command);

                List<String> matches = new List<string>();

                foreach (Match match in coll)
                {
                    matches.Add(match.ToString());
                }

                foreach (String key in _params.Keys)
                {
                    if (!matches.Contains(key))
                        throw new Exception("The Param " + key + " does not exists in th SQL Statement!");
                }
                foreach (String match in matches)
                {
                    if (!_params.ContainsKey(match))
                        throw new Exception("The Value " + match + " does not exists in the Params!");

                    _oleDbCommand.Parameters.AddWithValue(match, _params[match] ?? DBNull.Value);
                }
            }
            catch (Exception ex)
            {
                LogParams();
                throw new ApplicationException("Fehler beim konfigurieren der der Parameter zur SQL Abfrage", ex);
            }
        }

        private Int32 ExecuteSqlNonQuery(String command)
        {
            Logger.GetInstance().LogMessage("Starte SQL Abfrage " + command, LogLevel.Debug);
            _command = command;
            _oleDbCommand = new OleDbCommand(_command, _connection);

            ConfigureParams();

            for (int i = 0; i <= 10; i++)
            {
                try
                {
                    _oleDbCommand.Connection.Open();

                    return _oleDbCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Thread.Sleep(i*100);
                    if (i == 10)
                    {
                        LogParams();
                        Logger.GetInstance().LogException("Fehler bei der Datenabfrage von " + _oleDbCommand.CommandText, ex, LogLevel.Exception);
                        throw;
                    }
                }
                finally
                {
                    _connection.Close();
                    i++;
                }
            }
            return 0;
        }

        private DataSet ExecuteSqlQuery(String command)
        {
            Logger.GetInstance().LogMessage("Starte SQL Abfrage " + command, LogLevel.Debug);
            _command = command;
            _oleDbCommand = new OleDbCommand(_command, _connection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(_oleDbCommand);

            ConfigureParams();

            DataSet dataSet = new DataSet();

            for (int i = 0; i <= 10; i++)
            {
                try
                {
                    dataAdapter.Fill(dataSet);
                    break;
                }
                catch (Exception ex)
                {
                    Thread.Sleep(i*100);
                    if (i == 10)
                    {
                        LogParams();
                        Logger.GetInstance().LogException("Fehler bei der Datenabfrage von " + _oleDbCommand.CommandText, ex, LogLevel.Exception);
                        throw;
                    }
                }
                finally
                {
                    _connection.Close();
                    i++;
                }
            }
            return dataSet;
        }

        private void LogParams()
        {
            StringBuilder sb = new StringBuilder();
            foreach (KeyValuePair<String, Object> item in _params)
            {
                if (item.Value == null )
                    sb.Append(" | " + item.Key + " = NULL");
                else if (item.Value == DBNull.Value )
                    sb.Append(" | " + item.Key + " = DBNULL");
                else
                    sb.Append(" | " + item.Key + " = '" + item.Value + "'");
            }
            Logger.GetInstance().LogMessage(sb.ToString(), LogLevel.Debug);
        }

        #endregion
    }
}